package com.unbank.bhi.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class BhiDaoImpl {
	private static Log logger = LogFactory.getLog(BhiDaoImpl.class);

	public static void executeUpdate(String sql) {
		// logger.info(sql);
		try {
			PreparedStatement stmt = null;
			Connection conn = null;
			conn = BhiJDBC.getConn();
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			conn.commit();
			stmt.close();
		} catch (Exception e) {
			logger.info("保存出错：" + e);
		}

	}

	public static int addDate(String sql) {
		int key = 0;
		try {
			PreparedStatement stmt = null;
			Connection conn = null;
			conn = BhiJDBC.getConn();
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				key = rs.getInt(1);
			}
			conn.commit();
			stmt.close();
		} catch (Exception e) {
			logger.info("保存出错：" + e);
		}
		return key;

	}

	// 删除表
	public static void deleteTable(String tableName) {
		StringBuffer dropTableSql = new StringBuffer();
		dropTableSql.append("DROP TABLE IF EXISTS `" + tableName + "`");
		executeUpdate(dropTableSql.toString());
	}

	// 创建一个表
	public static void makeTableSql(String tableName,
			Map<String, String> cloumNames) {
		StringBuffer createTableSQL = new StringBuffer();

		createTableSQL.append("CREATE TABLE " + tableName + " (");
		createTableSQL.append(" id int(11) NOT NULL AUTO_INCREMENT,");
		Set<String> cs = cloumNames.keySet();

		for (String e : cs) {

			String t = cloumNames.get(e);

			e = e.replaceAll("(?i)[^a-zA-Z0-9\u4E00-\u9FA5]", "");

			if (e.trim().isEmpty()) {
				continue;
			}
			if (e.contains("detailurl")) {

				createTableSQL.append(e + " varchar(255) DEFAULT NULL,");

				// } else if (t.length() > 10 && t.length() < 255) {
				//
				// createTableSQL.append(e + " varchar(255) DEFAULT NULL,");
				//
			} else {
				createTableSQL.append(e + " text DEFAULT NULL,");
			}
		}
		createTableSQL.append("TIME datetime NOT NULL , ");
		createTableSQL.append(" PRIMARY KEY (id) , UNIQUE KEY (detailurl)");
		createTableSQL.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
		executeUpdate(createTableSQL.toString());
	}

	// 插入数据
	public static void insertSql(String tableName,
			Map<String, String> cloumNames) {
		StringBuffer insertSQL = new StringBuffer();

		insertSQL.append("insert into " + tableName + " (");
		Set<String> cs = cloumNames.keySet();
		StringBuffer keys = new StringBuffer();
		StringBuffer values = new StringBuffer();
		for (String e : cs) {

			String value = cloumNames.get(e);
			e = e.replaceAll("(?i)[^a-zA-Z0-9\u4E00-\u9FA5]", "");
			keys.append(e + ",");
			values.append("'" + value + "',");

		}

		insertSQL.append(keys.toString());
		insertSQL.append(" TIME) values (");
		insertSQL.append(values.toString());
		insertSQL.append(" NOW())");
		executeUpdate(insertSQL.toString());
	}

}
